package com.jsj.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jsj.dao.IUserDao;
import com.jsj.model.User;
import com.jsj.utils.DBUtils;

/**
 * ctrl shift o
 * @author Administrator
 *
 */

public class UserDaoImpl implements IUserDao {

	@Override
	public int add(User user) {		
		String sql="insert into tuser(username,password)values('"+user.getUsername()+"','"+user.getPassword()+"')";		
		return DBUtils.updateBySql(sql);			
	}

	@Override
	public int delete(int id) {
		Connection conn=null;
		Statement st=null;
		ResultSet rs=null;
		int  ret=0;
		//alt+/
		conn=DBUtils.getConnecton();
		//通过conn拿到st，createStatement
		try {
			st=conn.createStatement();
			//statement中有两个方法
			//其中executeQuery方法用于查询
			//executeUpdate方法用于增、删、改
			String sql="delete from tuser where id="+id;			
			ret=st.executeUpdate(sql);
						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	@Override
	public int edit(User user) {
		Connection conn=null;
		Statement st=null;
		ResultSet rs=null;
		int  ret=0;
		//alt+/
		conn=DBUtils.getConnecton();
		//通过conn拿到st，createStatement
		try {
			st=conn.createStatement();
			//statement中有两个方法
			//其中executeQuery方法用于查询
			//executeUpdate方法用于增、删、改"delete from tuser where id="+id;	
			//update tuser set username='tom1',password='12345' where id=1
			String sql="update tuser set username='"+user.getUsername()+"',password='"+user.getPassword()+"' where id="+user.getId();
			ret=st.executeUpdate(sql);
						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	@Override
	public User get(int id) {
		Connection conn=null;
		Statement st=null;
		ResultSet rs=null;	
		
		User u=new User();		
		conn=DBUtils.getConnecton();
		//通过conn拿到st，createStatement
		try {
			st=conn.createStatement();
			//statement中有两个方法
			//其中executeQuery方法用于查询
			//executeUpdate方法用于增、删、改
			String sql="select * from tuser where id="+id;			
			rs=st.executeQuery(sql);			
			while(rs.next()) {
				int userId=rs.getInt("id");				
				String username=rs.getString("username");
				String password=rs.getString("password");
				u.setId(userId);
				u.setUsername(username);
				u.setPassword(password);						
			}						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
	}

	@Override
	public List<User> getAll() {
		Connection conn=null;
		Statement st=null;
		ResultSet rs=null;		
		
		List<User> list=new ArrayList<User>();
		
		conn=DBUtils.getConnecton();
		//通过conn拿到st，createStatement
		try {
			st=conn.createStatement();
			//statement中有两个方法
			//其中executeQuery方法用于查询
			//executeUpdate方法用于增、删、改
			String sql="select * from tuser";			
			rs=st.executeQuery(sql);			
			while(rs.next()) {
				User u=new User();
				int userId=rs.getInt("id");				
				String username=rs.getString("username");
				String password=rs.getString("password");
				u.setId(userId);
				u.setUsername(username);
				u.setPassword(password);	
				list.add(u);
			}						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
